package com.surelution.projects.crm
import java.text.NumberFormat
import groovy.time.TimeCategory

import java.util.Date;

class GasReportService {

    static transactional = true
	/**
	 * 非油销售清单(片区，站名，饮料，香烟……总收入，毛利，毛利率)
	 * @param startDay
	 * @param endDay
	 * @param areaIds
	 * @return
	 */
	def noGasReport(Date startDay, Date endDay,areaIds){
		def result
		def sb = new StringBuilder("select ngs.station.stationArea,ngs.station")
		NoGasTypeCategory.list().each {typeId->
			sb.append ", sum(case when ngs.noGasType.typeCategoty.name = '${typeId.name}' then ngs.income else 0 end) "
		}
		sb.append '''
				, sum(ngs.income)
				, sum(ngs.maoLi)
				, sum(ngs.maoLi)
				from NoGasSale ngs where ngs.saleTime <= :endDay and ngs.saleTime >= :startDay and ngs.station.stationArea.id in(:areaIds) group by ngs.station
				order by sum(ngs.income) DESC'''
		result = NoGasSale.executeQuery(sb.toString(),['startDay':startDay, 'endDay':endDay,'areaIds':areaIds])
		def typeSize = NoGasTypeCategory.list().size()
		result.each {
			def i = 0
			NoGasTypeCategory.list().each {st->
				it[typeSize-3+i] = parseNumber(it[typeSize-3+i].toString(), 1)
				i++
			}
				it[typeSize+1] = parseNumber(it[typeSize+1].toString(), 1)
				it[typeSize+2] = parseNumber(it[typeSize+2].toString(), 1)
				it[typeSize+3] = parseNumber((it[typeSize+3]).toString(), 1)
			if (it[typeSize+3]){
				it[typeSize+4] = parseNumber((it[typeSize+3]/it[typeSize+2]*100).toString(),1)
			}
			else it[typeSize+3] = 0
		}
		result.sort(){
			-it[typeSize+4]
		}
		return result
    }
	
	/**
	 * 非油计划完成率(片区，站名，计划总量)
	 * @param startDay
	 * @param endDay
	 * @param areaIds
	 * @return(片区、站名、计划量、总收入、计划完成率、毛利)
	 */
	def noGasPlanReport(Date startDay, Date endDay,areaIds){
		def result,pResult
		Date month = new Date(startDay.year,startDay.month,1)
		use(TimeCategory) {
			if(endDay - 32.days <= startDay) month = new Date(endDay.year,endDay.month,1)
		}
		def spb = new StringBuilder("select ngps.station.stationArea,ngps.station")
		spb.append ''', sum(ngps.planNums)
				from NoGasPlanSum ngps where ngps.planTime <= :endDay and ngps.planTime >= :startDay and ngps.station.stationArea.id in(:areaIds) group by ngps.station'''
		result = NoGasPlanSum.executeQuery(spb.toString(),['startDay':month, 'endDay':endDay,'areaIds':areaIds])
		
		def sb = new StringBuilder("select ngs.station.stationArea,ngs.station")
		sb.append '''
				, sum(ngs.maoLi)
				, sum(ngs.income)
				, sum(ngs.maoLi)
				, sum(ngs.maoLi)
				from NoGasSale ngs where ngs.saleTime <= :endDay and ngs.saleTime >= :startDay and ngs.station.stationArea.id in(:areaIds) group by ngs.station'''
		pResult = NoGasSale.executeQuery(sb.toString(),['startDay':startDay, 'endDay':endDay,'areaIds':areaIds])
		if (!result&&!pResult){
			return 0
		}
		def map = new HashMap()
		result.each{
			map.put(it[1],parseNumber(it[2].toString(),1))
		}
		pResult.each {
			if (map.get(it[1])){
				it[2] = parseNumber(map.get(it[1]).toString(), 1)
				it[3] = parseNumber(it[3].toString(), 1)
				it[4] = parseNumber(it[4].toString(), 1)
				it[5] = parseNumber((it[3]/it[2]*100).toString(),1)
			}
			else {
				it[2] = 0
				it[3] = parseNumber(it[3].toString(), 1)
				it[4] = parseNumber(it[4].toString(), 1)
				it[5] = 0
			}
		}
		pResult.sort(){
			-it[5]
		}
		println "GasReportService:${pResult}"
		return pResult
	}
	
	/**
	 * 开卡量完成率，(站名、前-开卡总量、后-开卡总量，增长率,前计划量，后计划量，前完成率，后完成率)
	 * @param startDay
	 * @param endDay
	 * @param startDayA
	 * @param endDayA
	 * @param areaIds
	 * @return
	 */
	def cardReport(Date startDay ,Date endDay, Date startDayA, Date endDayA, String s){
		def result,cresult,cresultA,cresultB
		Date month = new Date(startDay.year,startDay.month,1)
		Date monthA = new Date(startDayA.year,startDayA.month,1)
		use(TimeCategory) {
			if(endDay - 32.days <= startDay) month = new Date(endDay.year,endDay.month,1)
			if(endDayA - 32.days <= startDayA) monthA = new Date(endDayA.year,endDayA.month,1)
			if(startDayA.year!=endDayA.year&&s=="m") {
				monthA = new Date(endDayA.year,endDayA.month,1)
			}
		}
		if 	(startDay>endDay||startDayA>endDayA){
			return 0
		}
		String hql = '''select cl.station,
						count(cl.cardNo),
						count(cl.cardNo),
						count(cl.cardNo),
						count(cl.cardNo),
						count(cl.cardNo),
						count(cl.cardNo),
						count(cl.cardNo)
						from CardList cl where cl.assignTime <= :endDay and cl.assignTime >= :startDay and cl.station.id!=78 group by cl.station'''
		result = CardList.executeQuery(hql,['startDay':startDay, 'endDay':endDay])
		String shql = '''select cl.station,
						count(cl.cardNo)
						from CardList cl where cl.assignTime <= :endDayA and cl.assignTime >= :startDayA and cl.station.id!=78 group by cl.station'''
		cresult = GasSale.executeQuery(shql,['startDayA':startDayA, 'endDayA':endDayA])
		String sqlA = '''select cpl.station,
				sum(cpl.planNumber)
				from CardPlanList cpl where cpl.planTime <= :endDay and cpl.planTime >= :startDay and cpl.station.id!=78 group  by cpl.station'''
		cresultA = CardPlanList.executeQuery(sqlA,['startDay':month, 'endDay':endDay])
		String sqlB = '''select cpl.station,
				sum(cpl.planNumber)
				from CardPlanList cpl where cpl.planTime <= :endDayA and cpl.planTime >= :startDayA and cpl.station.id!=78 group by cpl.station'''
		cresultB = CardPlanList.executeQuery(sqlB,['startDayA':monthA, 'endDayA':endDayA])
		def cardSale = new HashMap()
		def cardPlanSaleA = new HashMap()
		def cardPlanSaleB = new HashMap()
		cresult.each {
			cardSale.put(it[0], it[1])
		}
		cresultA.each {
			cardPlanSaleA.put(it[0], it[1])
		}
		cresultB.each {
			cardPlanSaleB.put(it[0], it[1])
		}
		result.each {
			it[2] = cardSale.get(it[0])?cardSale.get(it[0]):0
			it[4] = cardPlanSaleA.get(it[0])?cardPlanSaleA.get(it[0]):0
			it[5] = cardPlanSaleB.get(it[0])?cardPlanSaleB.get(it[0]):0
			if (it[2]){
				it[3] = (it[2]-it[1])/it[1]*100
			}
			else it[3] = 100
			if (it[2]!=0&&it[5]!=0){
				it[7] = (it[2])/it[5]*100
			}
			else it[7] =0
			if (it[1]!=0&&it[4]!=0){
				it[6] = (it[1])/it[4]*100
			}
			else it[7] =0
		}
		result.sort {
			-it[3]
		}
		if (!result){
			return 0
		}
		return result
	}
	
	/**
	 * 月度卡销比
	 * @param startDay
	 * @param endDay
	 * @return
	 */
	def cardPropReport(Date startDay ,Date endDay){
		def cresult,cardPropList
		String shql = '''select gs.station,
						sum(gs.icCardAmount),
						sum(gs.actualAmount),
						sum(gs.actualAmount)
						from GasSale gs where gs.saleTime <= :endDay and gs.saleTime >= :startDay group by gs.station'''
		cresult = GasSale.executeQuery(shql,['startDay':startDay, 'endDay':endDay])
		cresult.each {
			if (it[2]){
				it[3] = it[1]/it[2]*100
			}
			else it[3] = 100
		}
		cresult.sort {
			-it[3]
		}
		if (!cresult){
			return 0
			}
		return cresult
	}
	
/* 处理float数值，最大保留3位
	* @param s
	* @param i
	* @return
	*/
   def parseNumber(String s,int i){
	   NumberFormat nf = NumberFormat.getInstance();
	   float d = Float.valueOf(s);
	   nf.setMinimumFractionDigits(i);
	   nf.setMaximumFractionDigits(i);
	   def number = nf.parse(nf.format(d)).floatValue()
	   return number
   }
   /**
    * 判断是否存在该站的数据，如不存在，返回0，否则，返回匹配该站的记录
    * @param mapList
    * @param station
    * @return
    */
   int isExistHash(mapList,OwnStation station){
	   def flag = 0
	   mapList.each{
		   if(it.get("station") == station){
			   flag = 1
		   }
	   }
	   return flag
   }
   
   int isExist(mapList,OwnStation station,int i){
	   def flag = 0
	   if (mapList == 0) return 0
	   else{
		   mapList.each{
			   if(it[i] == station){
				   flag = 1
			   }
		   }
	   }
	   return flag
   }
}
